BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.tbl_Aula
	(
	aulaId int NOT NULL IDENTITY (1, 1),
	nombre varchar(50) NOT NULL,
	estado varchar(1) NOT NULL,
	descripcion varchar(4000) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.tbl_Aula ADD CONSTRAINT
	PK_tbl_Aula PRIMARY KEY CLUSTERED 
	(
	aulaId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.tbl_Aula SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'dbo.tbl_Aula', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.tbl_Aula', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.tbl_Aula', 'Object', 'CONTROL') as Contr_Per 
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 19/11/2011
-- Description:	Devuelve un aula segun su id
-- =============================================
CREATE PROCEDURE usp_AULA_GetAulaById
	@intAulaId	int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT [aulaId]
		  ,[nombre]
		  ,[estado]
		  ,[descripcion]
	FROM [dbo].[tbl_Aula]
	WHERE	[aulaId] = @intAulaId 


END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 19/11/2011
-- Description:	Devuelve las aulas segun la busqueda
-- =============================================
CREATE PROCEDURE usp_AULA_GetAulaBySearch
	@varSearchTerm	VARCHAR(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT [aulaId]
		  ,[nombre]
		  ,[estado]
		  ,[descripcion]
	FROM [dbo].[tbl_Aula]
	WHERE	[nombre] LIKE '%' + @varSearchTerm + '%'
			OR [descripcion]  LIKE '%' + @varSearchTerm + '%'


END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 19/11/2011
-- Description:	Inserta un aula
-- =============================================
CREATE PROCEDURE usp_AULA_InsertarAula
	@intAulaId		INT	OUTPUT,
	@varNombre		VARCHAR(50),
	@varEstado		VARCHAR(1),
	@varDescripcion	VARCHAR(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    BEGIN TRANSACTION

    BEGIN TRY
    
		INSERT INTO [COLEGIO].[dbo].[tbl_Aula]
           ([nombre]
           ,[estado]
           ,[descripcion])
		 VALUES
			   (@varNombre
			   ,@varEstado
			   ,@varDescripcion)
		
		SET @intAulaId = SCOPE_IDENTITY()
    
		COMMIT TRANSACTION
		
	END TRY
	BEGIN CATCH
	
		ROLLBACK TRANSACTION
	
	END CATCH

END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 19/11/2011
-- Description:	Elimina un aula
-- =============================================
CREATE PROCEDURE usp_AULA_EliminarAula
	@intAulaId		INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    BEGIN TRANSACTION	

    BEGIN TRY
    
		DELETE FROM [COLEGIO].[dbo].[tbl_Aula]
		WHERE [aulaId] = @intAulaId
		
		COMMIT TRANSACTION
		
	END TRY
	BEGIN CATCH
	
		ROLLBACK TRANSACTION
		
	END CATCH

END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 19/11/2011
-- Description:	Actualiza un aula
-- =============================================
CREATE PROCEDURE [dbo].[usp_AULA_ActualizarAula]
	@intAulaId		INT,
	@varNombre		VARCHAR(50),
	@varEstado		VARCHAR(1),
	@varDescripcion	VARCHAR(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    BEGIN TRANSACTION	

    BEGIN TRY
    
		UPDATE [COLEGIO].[dbo].[tbl_Aula]
	   SET [nombre] = @varNombre
		  ,[estado] = @varEstado
		  ,[descripcion] = @varDescripcion
		WHERE [aulaId] = @intAulaId
		
		COMMIT TRANSACTION
		
	END TRY
	BEGIN CATCH
	
		ROLLBACK TRANSACTION
		
	END CATCH

END
GO


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.tbl_Version
	(
	versionMayor int NOT NULL,
	versionMenor int NOT NULL,
	release int NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.tbl_Version SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'dbo.tbl_Version', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.tbl_Version', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.tbl_Version', 'Object', 'CONTROL') as Contr_Per 
GO

INSERT INTO tbl_Version VALUES(1,1,0)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Eduardo Murillo
-- Create date: 20/11/2011
-- Description:	Obtiene la version del sistema
-- =============================================
CREATE PROCEDURE usp_VERSION_GetVersion
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT TOP 1
	   [versionMayor]
      ,[versionMenor]
      ,[release]
	FROM [dbo].[tbl_Version]
	
END
GO
